"""数据库初始化脚本"""

import asyncio
import sys
import os
from pathlib import Path

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from app.core.config.settings import settings
from app.domains.models import Base, User, Device, WorkOrder, Alert
from app.core.security.auth import get_password_hash
from app.shared.constants.enums import UserRole, DeviceType, DeviceStatus
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def create_database_if_not_exists():
    """如果数据库不存在则创建"""
    try:
        # 解析数据库URL
        db_url_parts = settings.DATABASE_URL.split('/')
        db_name = db_url_parts[-1]
        base_url = '/'.join(db_url_parts[:-1])
        
        # 连接到MySQL服务器（不指定数据库）
        engine = create_engine(base_url + '/mysql')
        
        with engine.connect() as conn:
            # 检查数据库是否存在
            result = conn.execute(text(f"SHOW DATABASES LIKE '{db_name}'"))
            if not result.fetchone():
                # 创建数据库
                conn.execute(text(f"CREATE DATABASE {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"))
                logger.info(f"✅ 数据库 {db_name} 创建成功")
            else:
                logger.info(f"📋 数据库 {db_name} 已存在")
                
        engine.dispose()
    except Exception as e:
        logger.error(f"❌ 创建数据库失败: {e}")
        raise


def create_tables():
    """创建所有表"""
    try:
        engine = create_engine(settings.DATABASE_URL, echo=settings.DATABASE_ECHO)
        
        # 创建所有表
        Base.metadata.create_all(bind=engine)
        logger.info("✅ 数据库表创建成功")
        
        engine.dispose()
    except Exception as e:
        logger.error(f"❌ 创建数据库表失败: {e}")
        raise


def create_admin_user():
    """创建管理员用户"""
    try:
        engine = create_engine(settings.DATABASE_URL)
        
        with engine.connect() as conn:
            # 检查管理员用户是否已存在
            result = conn.execute(
                text("SELECT id FROM users WHERE username = 'admin'")
            )
            if result.fetchone():
                logger.info("👤 管理员用户已存在")
                return
            
            # 创建管理员用户
            admin_data = {
                'username': 'admin',
                'email': 'admin@meishen.com',
                'phone': '13800138000',
                'password_hash': get_password_hash('admin123'),
                'full_name': '系统管理员',
                'role': UserRole.ADMIN.value,
                'is_active': True
            }
            
            conn.execute(text("""
                INSERT INTO users (username, email, phone, password_hash, full_name, role, is_active, created_at, updated_at)
                VALUES (:username, :email, :phone, :password_hash, :full_name, :role, :is_active, NOW(), NOW())
            """), admin_data)
            
            conn.commit()
            logger.info("✅ 管理员用户创建成功 (username: admin, password: admin123)")
            
        engine.dispose()
    except Exception as e:
        logger.error(f"❌ 创建管理员用户失败: {e}")
        raise


def create_sample_devices():
    """创建示例设备"""
    try:
        engine = create_engine(settings.DATABASE_URL)
        
        with engine.connect() as conn:
            # 检查是否已有设备
            result = conn.execute(text("SELECT COUNT(*) as count FROM devices"))
            count = result.fetchone()[0]
            if count > 0:
                logger.info("🚽 示例设备已存在")
                return
            
            # 创建示例设备
            sample_devices = [
                {
                    'device_code': 'TOILET_A001',
                    'device_name': 'A区公厕001',
                    'device_type': DeviceType.PORTABLE_TOILET.value,
                    'location_name': 'A区入口处',
                    'latitude': 31.230416,
                    'longitude': 121.473701,
                    'status': DeviceStatus.ONLINE.value,
                    'materials_level': 85
                },
                {
                    'device_code': 'TOILET_A002',
                    'device_name': 'A区公厕002',
                    'device_type': DeviceType.PORTABLE_TOILET.value,
                    'location_name': 'A区停车场',
                    'latitude': 31.230500,
                    'longitude': 121.473800,
                    'status': DeviceStatus.ONLINE.value,
                    'materials_level': 60
                },
                {
                    'device_code': 'TOILET_B001',
                    'device_name': 'B区公厕001',
                    'device_type': DeviceType.PORTABLE_TOILET.value,
                    'location_name': 'B区办公楼',
                    'latitude': 31.231000,
                    'longitude': 121.474000,
                    'status': DeviceStatus.FAULT.value,
                    'materials_level': 20
                }
            ]
            
            for device in sample_devices:
                conn.execute(text("""
                    INSERT INTO devices (device_code, device_name, device_type, location_name, 
                                       latitude, longitude, status, materials_level, created_at, updated_at)
                    VALUES (:device_code, :device_name, :device_type, :location_name,
                           :latitude, :longitude, :status, :materials_level, NOW(), NOW())
                """), device)
            
            conn.commit()
            logger.info("✅ 示例设备创建成功")
            
        engine.dispose()
    except Exception as e:
        logger.error(f"❌ 创建示例设备失败: {e}")
        raise


def main():
    """主函数"""
    logger.info("🚀 开始初始化美申智控系统数据库...")
    
    try:
        # 1. 创建数据库（如果不存在）
        create_database_if_not_exists()
        
        # 2. 创建表结构
        create_tables()
        
        # 3. 创建管理员用户
        create_admin_user()
        
        # 4. 创建示例设备
        create_sample_devices()
        
        logger.info("🎉 数据库初始化完成！")
        logger.info("📝 管理员登录信息：")
        logger.info("   用户名: admin")
        logger.info("   密码: admin123")
        
    except Exception as e:
        logger.error(f"💥 数据库初始化失败: {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()